-- Update existing records
Update pwl
Set FirstSpeechLink = fsl.FirstSpeechLink
From ParliamentarianLinks.dbo.FirstSpeechLinks as fsl 
		Inner JOIN TESTParl2.dbo.Parliamentarians as p on RIGHT(fsl.MPID,3) = RIGHT(p.MPID, 3)
		INNER JOIN TESTPARL2.dbo.ParliamentarianWebLinks as pwl on p.CPALId = pwl.CPALId
Where RIGHT(fsl.MPID,3) = RIGHT(p.MPID, 3)
				

-- insert new records
insert into TESTParl2.dbo.ParliamentarianWebLinks (CPALId, FirstSpeechLink)
Select p.CPALId, fsl.FirstSpeechLink
From ParliamentarianLinks.dbo.FirstSpeechLinks as fsl 
		Left outer JOIN TESTParl2.dbo.Parliamentarians as p on RIGHT(fsl.MPID,3) = RIGHT(p.MPID, 3)
		left outer join TESTParl2.dbo.ParliamentarianWebLinks as pwl on p.CPALId = pwl.CPALId
WHERE pwl.CPALId is null and p.FormerMember = 0